package cn.hsa.fqz.task.controller;

import cn.hsa.fqz.task.controller.vo.ClientDsConfigVO;
import cn.hsa.fqz.task.entity.SysDsConfig;
import cn.hsa.fqz.task.service.SqlClientService;
import cn.hsa.fqz.task.service.dto.LoginUserDTO;
import cn.hsa.fqz.task.service.dto.SqlExecuteDTO;
import cn.hsa.fqz.task.service.dto.TTaskSqlExecuteDTO;
import cn.hsa.hsaf.core.framework.context.HsafContextHolder;
import cn.hsa.hsaf.core.framework.util.CurrentUser;
import cn.hsa.hsaf.core.framework.web.WrapperResponse;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * @author Administrator
 * @version 1.0
 * @date 2023-11-16 15:01
 */
@Slf4j
@Api(value = "脚本运维客户端", tags = "脚本运维客户端")
@RestController
@RequestMapping("/client")
public class SqlClientController {

    @Autowired
    private SqlClientService sqlClientService;

    @ApiOperation(value = "获取所有数据源配置信息", notes = "获取所有数据源配置信息")
    @GetMapping("/ds/list")
    public WrapperResponse<List<SysDsConfig>> getAll() {
        List<SysDsConfig> ls = sqlClientService.getAllDsConfig();
        return WrapperResponse.success(ls);
    }

    @ApiOperation(value = "新增数据源配置信息", notes = "新增数据源配置信息")
    @PostMapping("/ds/add")
    public WrapperResponse<Integer> addDs(@RequestBody ClientDsConfigVO vo) {
        Integer result = sqlClientService.addDs(vo);
        return WrapperResponse.success(result);
    }

    @ApiOperation(value = "修改数据源配置信息", notes = "修改数据源配置信息")
    @PostMapping("/ds/update")
    public WrapperResponse<Integer> updateDs(@RequestBody ClientDsConfigVO vo) {
        Integer result = sqlClientService.updateDs(vo);
        return WrapperResponse.success(result);
    }

    @ApiOperation(value = "删除数据源配置信息", notes = "删除数据源配置信息")
    @DeleteMapping("/ds/delete")
    public WrapperResponse<Integer> deleteDs(@RequestParam("id") Integer dsId) {
        Integer result = sqlClientService.deleteDs(dsId);
        return WrapperResponse.success(result);
    }

    @ApiOperation(value = "执行SQL", notes = "执行SQL")
    @PostMapping("/execute/sql")
    public WrapperResponse<Object> executeSQL(@RequestBody SqlExecuteDTO execute) {
        if(StringUtils.isEmpty(execute.getDbCode())){
            return WrapperResponse.error(404, "数据源不能为空",null);
        }
       Object result = sqlClientService.startSqlExecute(execute);
        return WrapperResponse.success(result);
    }

    @ApiOperation(value = "执行SQL导出", notes = "执行SQL导出")
    @PostMapping("/execute/sql/export")
    public void executeSQLAndExport(@RequestBody SqlExecuteDTO execute, HttpServletResponse response) {
        try {
            String fileName =  "data-export-"+ DateFormatUtils.format(new Date(), "yyyy-MM-dd") + ".xlsx";
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment; filename=data_export"+fileName);
            OutputStream os = null;
            os = response.getOutputStream();
            sqlClientService.startSqlExecuteAndExport(execute,os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @ApiOperation(value = "执行SQL", notes = "执行SQL")
    @GetMapping("/execute/sql/odps")
    public WrapperResponse<Object> executeOdpsSQL(@RequestParam("sql")String sql) {
        Connection conn = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            Class.forName("com.aliyun.odps.jdbc.OdpsDriver");
            conn = DriverManager.getConnection("jdbc:odps:http://service.cn-beijing-ybcloud-d01.odps.cloud.hsip.gov.cn/api?project=max_compute_fqz_prd&useProjectTimeZone=true;",
                    "Is6RJqYbHuyeo2sc","fcY9vBPiP7A3xcYyryhHTFy6CArqnZ");
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> row = new TreeMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    row.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));
                }
                list.add(row);
            }
        }catch(Exception e){
            log.error(e.getMessage());
        }
        return WrapperResponse.success(list);
    }

}
